﻿using System.Collections;
using System.Collections.Generic;
using System;
using System.Text;

using System.Web.UI;
using DAL;
using BLL;
using System.IO;
using System.Data;
using System.Xml;
using System.Web.Security;
using System.Security.Cryptography;
using System.Net;
using System.Web;
using ICSharpCode.SharpZipLib.Zip.Compression;
using ICSharpCode.SharpZipLib.Zip.Compression.Streams;
using NPOI.HSSF.UserModel;

namespace Web.Old_App_Code
{

    /// <summary>
    ///ExcelHelper 的摘要说明
    /// </summary>
    public class ExcelHelper
    {
        // Methods
        private static Stream ExportDataSetToExcel(DataSet sourceDs, string sheetName)
        {
            HSSFWorkbook workbook = new HSSFWorkbook();
            MemoryStream stream = new MemoryStream();
            string[] strArray = sheetName.Split(new char[] { ',' });
            for (int i = 0; i < strArray.Length; i++)
            {
                HSSFSheet sheet = workbook.CreateSheet(strArray[i]) as HSSFSheet;
                HSSFRow row = sheet.CreateRow(0) as HSSFRow;
                foreach (DataColumn column in sourceDs.Tables[i].Columns)
                {
                    row.CreateCell(column.Ordinal).SetCellValue(column.ColumnName);
                }
                int rownum = 1;
                foreach (DataRow row2 in sourceDs.Tables[i].Rows)
                {
                    HSSFRow row3 = sheet.CreateRow(rownum) as HSSFRow;
                    foreach (DataColumn column in sourceDs.Tables[i].Columns)
                    {
                        row3.CreateCell(column.Ordinal).SetCellValue(row2[column].ToString());
                    }
                    rownum++;
                }
            }
            workbook.Write(stream);
            stream.Flush();
            stream.Position = 0L;
            workbook = null;
            return stream;
        }

        public static void ExportDataSetToExcel(DataSet sourceDs, string fileName, string sheetName)
        {
            MemoryStream stream = ExportDataSetToExcel(sourceDs, sheetName) as MemoryStream;
            HttpContext.Current.Response.AppendHeader("Content-Disposition", "attachment;filename=" + fileName);
            HttpContext.Current.Response.BinaryWrite(stream.ToArray());
            HttpContext.Current.Response.End();
            stream.Close();
            stream = null;
        }

        private static Stream ExportDataTableToExcel(DataTable sourceTable, string sheetName)
        {
            HSSFWorkbook workbook = new HSSFWorkbook();
            MemoryStream stream = new MemoryStream();
            HSSFSheet sheet = workbook.CreateSheet(sheetName) as HSSFSheet;
            HSSFRow row = sheet.CreateRow(0) as HSSFRow;
            foreach (DataColumn column in sourceTable.Columns)
            {
                row.CreateCell(column.Ordinal).SetCellValue(column.ColumnName);
            }
            int rownum = 1;
            foreach (DataRow row2 in sourceTable.Rows)
            {
                HSSFRow row3 = sheet.CreateRow(rownum) as HSSFRow;
                foreach (DataColumn column in sourceTable.Columns)
                {
                    row3.CreateCell(column.Ordinal).SetCellValue(row2[column].ToString());
                }
                rownum++;
            }
            workbook.Write(stream);
            stream.Flush();
            stream.Position = 0L;
            sheet = null;
            row = null;
            workbook = null;
            return stream;
        }

        public static void ExportDataTableToExcel(DataTable sourceTable, string fileName, string sheetName)
        {
            MemoryStream stream = ExportDataTableToExcel(sourceTable, sheetName) as MemoryStream;
            HttpContext.Current.Response.AppendHeader("Content-Disposition", "attachment;filename=" + fileName);
            HttpContext.Current.Response.BinaryWrite(stream.ToArray());
            HttpContext.Current.Response.End();
            stream.Close();
            stream = null;
        }

        public static DataSet ImportDataSetFromExcel(Stream excelFileStream, int headerRowIndex)
        {
            DataSet set = new DataSet();
            HSSFWorkbook workbook = new HSSFWorkbook(excelFileStream);
            int index = 0;
            int numberOfSheets = workbook.NumberOfSheets;
            while (index < numberOfSheets)
            {
                HSSFSheet sheetAt = workbook.GetSheetAt(index) as HSSFSheet;
                DataTable table = new DataTable();
                HSSFRow row = sheetAt.GetRow(headerRowIndex) as HSSFRow;
                int lastCellNum = row.LastCellNum;
                int firstCellNum = row.FirstCellNum;
                while (firstCellNum < lastCellNum)
                {
                    if ((row.GetCell(firstCellNum) == null) || (row.GetCell(firstCellNum).StringCellValue.Trim() == ""))
                    {
                        lastCellNum = firstCellNum + 1;
                        break;
                    }
                    DataColumn column = new DataColumn(row.GetCell(firstCellNum).StringCellValue);
                    table.Columns.Add(column);
                    firstCellNum++;
                }
                for (firstCellNum = sheetAt.FirstRowNum + 1; firstCellNum <= sheetAt.LastRowNum; firstCellNum++)
                {
                    HSSFRow row2 = sheetAt.GetRow(firstCellNum) as HSSFRow;
                    if (((row2 == null) || (row2.GetCell(0) == null)) || (row2.GetCell(0).ToString().Trim() == ""))
                    {
                        break;
                    }
                    DataRow row3 = table.NewRow();
                    for (int i = row2.FirstCellNum; i < lastCellNum; i++)
                    {
                        if (row2.GetCell(i) != null)
                        {
                            row3[i] = row2.GetCell(i).ToString();
                        }
                    }
                    table.Rows.Add(row3);
                }
                set.Tables.Add(table);
                index++;
            }
            excelFileStream.Close();
            workbook = null;
            return set;
        }

        public static DataSet ImportDataSetFromExcel(string excelFilePath, int headerRowIndex)
        {
            using (FileStream stream = File.OpenRead(excelFilePath))
            {
                return ImportDataSetFromExcel(stream, headerRowIndex);
            }
        }

        public static DataTable ImportDataTableFromExcel(Stream excelFileStream, int sheetIndex, int headerRowIndex)
        {
            int num2;
            HSSFWorkbook workbook = new HSSFWorkbook(excelFileStream);
            HSSFSheet sheetAt = workbook.GetSheetAt(sheetIndex) as HSSFSheet;
            DataTable table = new DataTable();
            HSSFRow row = sheetAt.GetRow(headerRowIndex) as HSSFRow;
            int lastCellNum = row.LastCellNum;
            for (num2 = row.FirstCellNum; num2 < lastCellNum; num2++)
            {
                if ((row.GetCell(num2) == null) || (row.GetCell(num2).StringCellValue.Trim() == ""))
                {
                    lastCellNum = num2 + 1;
                    break;
                }
                DataColumn column = new DataColumn(row.GetCell(num2).StringCellValue);
                table.Columns.Add(column);
            }
            for (num2 = sheetAt.FirstRowNum + 1; num2 <= sheetAt.LastRowNum; num2++)
            {
                HSSFRow row2 = sheetAt.GetRow(num2) as HSSFRow;
                if (((row2 == null) || (row2.GetCell(0) == null)) || (row2.GetCell(0).ToString().Trim() == ""))
                {
                    break;
                }
                DataRow row3 = table.NewRow();
                for (int i = row2.FirstCellNum; i < lastCellNum; i++)
                {
                    row3[i] = row2.GetCell(i);
                }
                table.Rows.Add(row3);
            }
            excelFileStream.Close();
            workbook = null;
            sheetAt = null;
            return table;
        }

        public static DataTable ImportDataTableFromExcel(Stream excelFileStream, string sheetName, int headerRowIndex)
        {
            HSSFWorkbook workbook = new HSSFWorkbook(excelFileStream);
            HSSFSheet sheet = workbook.GetSheet(sheetName) as HSSFSheet;
            DataTable table = new DataTable();
            HSSFRow row = sheet.GetRow(headerRowIndex) as HSSFRow;
            int lastCellNum = row.LastCellNum;
            try
            {
                int num2;
                for (num2 = row.FirstCellNum; num2 < lastCellNum; num2++)
                {
                    DataColumn column = new DataColumn(row.GetCell(num2).StringCellValue);
                    table.Columns.Add(column);
                }
                for (num2 = sheet.FirstRowNum + 1; num2 <= sheet.LastRowNum; num2++)
                {
                    HSSFRow row2 = sheet.GetRow(num2) as HSSFRow;
                    DataRow row3 = table.NewRow();
                    for (int i = row2.FirstCellNum; i < lastCellNum; i++)
                    {
                        row3[i] = row2.GetCell(i);
                    }
                    table.Rows.Add(row3);
                }
            }
            catch
            {
            }
            excelFileStream.Close();
            workbook = null;
            sheet = null;
            return table;
        }

        public static DataTable ImportDataTableFromExcel(string excelFilePath, int sheetIndex, int headerRowIndex)
        {
            using (FileStream stream = File.OpenRead(excelFilePath))
            {
                return ImportDataTableFromExcel(stream, sheetIndex, headerRowIndex);
            }
        }

        public static DataTable ImportDataTableFromExcel(string excelFilePath, string sheetName, int headerRowIndex)
        {
            using (FileStream stream = File.OpenRead(excelFilePath))
            {
                return ImportDataTableFromExcel(stream, sheetName, headerRowIndex);
            }
        }

    }

}